<?php
/**
 * @package		简易CMS
 * @subpackage	数据库管理模块
 * @version		$Id: admincp_db.php 23 2012-06-11 18:04:03Z htmambo@gmail.com $
 * @author		Hoping
 * @copyright	Copyright (C) 2010 Hoping Software Studio.
 */


if(!defined('IN_JYCMS') || !defined('IN_ADMINCP')) {
	exit('Access Denied');
}

$db = & DB::object();

$tabletype = $db->version() > '4.1' ? 'Engine' : 'Type';
$tablepre = $_G['config']['db'][1]['tablepre'];
$dbcharset = $_G['config']['db'][1]['dbcharset'];

cpheader();

if(!isfounder()) cpmsg('noaccess_isfounder', '', 'error');


$excepttables = array($tablepre.'adminsessions', $tablepre.'failedlogins', $tablepre.'searchindex', $tablepre.'sessions');

$query = DB::query("SELECT skey, svalue FROM #__common_setting WHERE skey IN ('backupdir', 'custombackup')");
while($var = DB::fetch($query)) {
	${$var['skey']} = $var['svalue'];
}
shownav('founder', 'nav_db', 'nav_db_'.$operation);
showsubmenu('nav_db', array(
			array('nav_db_runquery', 'db&operation=runquery', $operation=='runquery'),
			array('nav_db_optimize', 'db&operation=optimize', $operation=='optimize')

));
if($operation == 'runquery') {

	$checkperm = checkpermission('runquery', 0);

	$runquerys = array();

	if(!submitcheck('sqlsubmit')) {
		showtips('db_runquery_tips');
			showtableheader();
			showformheader('db&operation=runquery&option=simple');
		if($runqueryselect){
			showsetting('db_runquery_simply', '', '', $runqueryselect);
			showsetting('', '', '', '<input type="checkbox" class="checkbox" name="createcompatible" value="1" checked="checked" />'.cplang('db_runquery_createcompatible'));
			showsubmit('sqlsubmit');
		}
			showformfooter();
		if($checkperm) {
			showformheader('db&operation=runquery&option=');
			showsetting('db_runquery_sql', '', '', '<textarea cols="85" rows="10" name="queries" style="width:500px;">'.$queries.'</textarea>');
			showsetting('', '', '', '<input type="checkbox" class="checkbox" name="createcompatible" value="1" checked="checked" />'.cplang('db_runquery_createcompatible'));
			showsubmit('sqlsubmit', 'submit', '', cplang('db_runquery_comment'));
			showformfooter();
		}

		showtablefooter();

	} else {
		$queries = $_G['gp_queries'];
		if($_G['gp_option'] == 'simple') {
			$queryselect = intval($_G['gp_queryselect']);
			$queries = isset($simplequeries[$queryselect]) && $simplequeries[$queryselect]['sql'] ? $simplequeries[$queryselect]['sql'] : '';
		} elseif(!$checkperm) {
			cpmsg('database_run_query_denied', '', 'error');
		}
		$sqlquery = splitsql(str_replace(array(' {tablepre}', ' cdb_', ' `cdb_', ' pre_', ' `pre_', ' #__'), array(' '.$tablepre, ' '.$tablepre, ' `'.$tablepre, ' '.$tablepre, ' `'.$tablepre, ' '.$tablepre), $queries));
		$affected_rows = 0;
		$sqllist = $query = array();

		foreach((array)$sqlquery as $sql) {
			if(trim($sql) != '') {
				$sql = !empty($_G['gp_createcompatible']) ? syntablestruct(trim($sql), $db->version() > '4.1', $dbcharset) : $sql;
				$isselect = strtolower(substr($sql, 0, 7)) == 'select ';
				$q = DB::query(dstripslashes($sql), 'SILENT');
				if($sqlerror = DB::error()) {
					break;
				} else {
					$affected_rows += intval(DB::affected_rows());
					if($isselect && $affected_rows<100) {
						$query[] = DB::fetch_all($q);
						$sqllist[] = $sql;
					}
				}
			}
		}
		if($query){

		shownav('founder', 'nav_db', 'nav_db_export');
		showsubmenu('nav_db', array(
			array('nav_db_export', 'db&operation=export', 0),
			array('nav_db_import', 'db&operation=import', 0),
			array('nav_db_runquery', 'db&operation=runquery', 1),
			array('nav_db_optimize', 'db&operation=optimize', 0),
			array('nav_db_dbcheck', 'db&operation=dbcheck', 0)

		));
			echo '<div class="itemtitle"><h3>'.lang('admincp_msg', 'database_run_query_succeed',array('affected_rows'=>$affected_rows)).'</h3></div>';

			foreach((array)$query as $k=>$v){
				$fields = array();
				showtableheader('<div style="float:left;">SQL: '.$sqllist[$k].'</div><div style="float: right;">Total '.count($v).' items</div>');

				$j = 0;
				$fields[] = '#';
				foreach((array)$v[0] as $key=>$var){
					$fields[] = $key;
					if($j>8) break;
					$j++;
				}
				showtablerow('', '', $fields);
				$i = 1;
				foreach((array)$v as  $var){
					$row = array($i);
					$j=0;
					foreach((array)$var as $f=>$vu){
						$row[] = $vu;
						if($j>8) break;
						$j++;
					}
					$i++;
					showtablerow('', '', $row);
				}
				showtablefooter();
			}
		} else
		$sqlerror ? cpmsg('database_run_query_invalid', '', 'error', array('sqlerror' => $sqlerror)) : cpmsg('database_run_query_succeed', '', 'succeed', array('affected_rows' => $affected_rows));

	}

} elseif($operation == 'optimize') {

	$optimizetable = '';
	$totalsize = 0;
	$tablearray = array( 0 => $tablepre);

	showtips('db_optimize_tips');
	showformheader('db&operation=optimize');
	showtableheader('db_optimize_tables');
	showsubtitle(array('', 'db_optimize_table_name', 'type', 'db_optimize_rows', 'db_optimize_data', 'db_optimize_index', 'db_optimize_frag'));

	if(!submitcheck('optimizesubmit')) {

		foreach((array)$tablearray as $tp) {
			$query = DB::query("SHOW TABLE STATUS LIKE '$tp%'", 'SILENT');
			while($table = DB::fetch($query)) {
				if($table['Data_free'] && $table[$tabletype] == 'MyISAM') {
					$checked = $table[$tabletype] == 'MyISAM' ? 'checked' : 'disabled';
					showtablerow('', '', array(
						"<input class=\"checkbox\" type=\"checkbox\" name=\"optimizetables[]\" value=\"$table[Name]\" $checked>",
						$table[Name],
						$table[$tabletype],
						$table[Rows],
						$table[Data_length],
						$table[Index_length],
						intval($table[Data_free]),
					));
					$totalsize += $table['Data_length'] + $table['Index_length'];
				}
			}
		}
		if(empty($totalsize)) {
			showtablerow('', 'colspan="10"', $lang['db_optimize_done']);
		} else {
			showtablerow('', 'colspan="10"', $lang['db_optimize_used'].' '.sizecount($totalsize));
			showsubmit('optimizesubmit', 'submit', '<input name="chkall" id="chkall" class="checkbox" onclick="checkAll(\'prefix\', this.form)" checked="checked" type="checkbox" /><label for="chkall">'.$lang[db_optimize_opt].'</label>');
		}

	} else {


		foreach((array)$tablearray as $tp) {
			$query = DB::query("SHOW TABLE STATUS LIKE '$tp%'", 'SILENT');
			while($table = DB::fetch($query)) {
				if(is_array($_G['gp_optimizetables']) && in_array($table['Name'], $_G['gp_optimizetables'])) {
					DB::query("OPTIMIZE TABLE $table[Name]");
				}
				showtablerow('', '', array(
					$table[Name],
					$db->version() > '4.1' ?  $table['Engine'] : $table['Type'],
					$table[Rows],
					$table[Data_length],
					$table[Index_length],
					0
				));
				$totalsize += $table['Data_length'] + $table['Index_length'];
			}
		}
		showtablerow('', 'colspan="10"', $lang['db_optimize_used'].' '.sizecount($totalsize));
	}

	showtablefooter();
	showformfooter();

}

function syntablestruct($sql, $version, $dbcharset) {

	if(strpos(trim(substr($sql, 0, 18)), 'CREATE TABLE') === FALSE) {
		return $sql;
	}

	$sqlversion = strpos($sql, 'ENGINE=') === FALSE ? FALSE : TRUE;

	if($sqlversion === $version) {

		return $sqlversion && $dbcharset ? preg_replace(array('/ character set \w+/i', '/ collate \w+/i', "/DEFAULT CHARSET=\w+/is"), array('', '', "DEFAULT CHARSET=$dbcharset"), $sql) : $sql;
	}

	if($version) {
		return preg_replace(array('/TYPE=HEAP/i', '/TYPE=(\w+)/is'), array("ENGINE=MEMORY DEFAULT CHARSET=$dbcharset", "ENGINE=\\1 DEFAULT CHARSET=$dbcharset"), $sql);

	} else {
		return preg_replace(array('/character set \w+/i', '/collate \w+/i', '/ENGINE=MEMORY/i', '/\s*DEFAULT CHARSET=\w+/is', '/\s*COLLATE=\w+/is', '/ENGINE=(\w+)(.*)/is'), array('', '', 'ENGINE=HEAP', '', '', 'TYPE=\\1\\2'), $sql);
	}
}

function splitsql($sql) {
	$sql = str_replace("\r", "\n", $sql);
	$ret = array();
	$num = 0;
	$queriesarray = explode(";\n", trim($sql));
	unset($sql);
	foreach((array)$queriesarray as $query) {
		$queries = explode("\n", trim($query));
		foreach((array)$queries as $query) {
			$ret[$num] .= $query[0] == "#" ? NULL : $query;
		}
		$num++;
	}
	return($ret);
}
